﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Top.Api;
using Top.Api.Request;
using Top.Api.Response;

namespace GgcmsTBK
{
    public partial class _Default : Page
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            string json = "", qtype = (Request.QueryString["qtype"] ?? "").ToLower();
            string url = ConfigurationManager.AppSettings["tbApiUrl"].ToString();
            string appkey = ConfigurationManager.AppSettings["tbAppKey"].ToString();
            string secret = ConfigurationManager.AppSettings["tbSecret"].ToString();
            long adzone_id = Tools.parseInt(ConfigurationManager.AppSettings["adzone_id"].ToString());
            string safekey1 = ConfigurationManager.AppSettings["safekey"] ?? "";
            string safekey2 = Request.QueryString["safekey"] ?? "";


            ITopClient client = new DefaultTopClient(url, appkey, secret, "json");

            Response.ContentType = "application/json";
            if (qtype == "coupon")
            {
                TbkDgItemCouponGetRequest req = GetDgItemCoupon();
                req.AdzoneId = adzone_id;

                TbkDgItemCouponGetResponse rsp = client.Execute(req);
                json = rsp.Body;
            }
            else if (qtype == "goods") {
                TbkItemGetRequest req = GetGoodsItem();

                TbkItemGetResponse rsp = client.Execute(req);
                json = rsp.Body;
            }
            else if (qtype == "info")
            {
                TbkItemInfoGetRequest req = GetItemInfo();
                TbkItemInfoGetResponse rsp = client.Execute(req);


                TbkCouponGetRequest creq = new TbkCouponGetRequest();
                creq.ItemId = Tools.ConvertType(req.NumIids, 0L);
                TbkCouponGetResponse crsp = client.Execute(creq);

                json = "{\"goods_info\":" + rsp.Body + ",\"coupon_info\":" + crsp.Body + "}";
            }
            else if (qtype == "tqg")
            {
                TbkJuTqgGetRequest req = GetJuTqg();
                req.AdzoneId = adzone_id;
                TbkJuTqgGetResponse rsp = client.Execute(req);
                json = rsp.Body;
            }
            else if (qtype == "update")
            {
                if (string.IsNullOrWhiteSpace(safekey2) || safekey2 != safekey1)
                {
                    json = "{\"error\":-3,\"message\":\"access denied\"}";
                }
                else
                {
                    json = FastJSON.JSON.ToJSON(XlsToDataBase());
                }
            }
            else if (qtype == "clear")
            {
                if (string.IsNullOrWhiteSpace(safekey2) || safekey2 != safekey1)
                {
                    json = "{\"error\":-3,\"message\":\"access denied\"}";
                }
                else
                {
                    json = Tools.JsonStringify(ClearDataBase());
                }
            }
            else if (qtype == "super")
            {
                try
                {
                    json = Tools.JsonStringify(GetSuperCoupon());
                }
                catch(Exception ex)
                {
                    Dictionary<string, dynamic> err = new Dictionary<string, dynamic>();
                    err.Add("error", 100);
                    err.Add("message", ex.Message);
                    json = Tools.JsonStringify(err);
                }
            }
            else
            {
                Response.StatusCode = 404;
                Response.ContentType = "text/plain";
            }
            //跨域
            if (Request.UrlReferrer != null)
            {
                Uri refHost = Request.UrlReferrer;
                Uri host = Request.Url;
                if (host.Host.ToLower() != refHost.Host.ToLower()||host.Port!=refHost.Port)
                {
                    string corsUrl = ConfigurationManager.AppSettings["CorsUrl"].ToString();
                    string[] urls = corsUrl.ToLower().Split(";".ToCharArray());
                    if (corsUrl == "*" || urls.Contains(refHost.Host.ToLower()))
                    {
                        Response.Headers["Access-Control-Allow-Methods"] = "GET,POST,PUT,DELETE,HEAD,OPTIONS";
                        Response.Headers["Access-Control-Allow-Origin"] = Request.UrlReferrer.Scheme + "://" + Request.UrlReferrer.Host + (Request.UrlReferrer.Port == 80 ? "" : ":" + Request.UrlReferrer.Port.ToString());
                        Response.Headers["Access-Control-Allow-Headers"] = "Content-Type,api_key,Authorization,X-Requested-With";
                    }
                }
            }
            Response.Write(json);
        }
        private TbkItemInfoGetRequest GetItemInfo()
        {
            int Platform = Tools.ConvertType(Request.QueryString["Platform"] ?? "1", 1);
            string NumIids = Request.QueryString["NumIids"];
            return new TbkItemInfoGetRequest()
            {
                Fields = "num_iid,title,pict_url,small_images,reserve_price,zk_final_price,user_type,provcity,item_url",
                NumIids = NumIids,
                Platform = Platform
            };
        }
        private TbkDgItemCouponGetRequest GetDgItemCoupon()
        {

            int Platform = Tools.ConvertType(Request.QueryString["Platform"] ?? "1", 1);
            int PageSize = Tools.ConvertType(Request.QueryString["PageSize"] ?? "10", 10);
            int PageNo = Tools.ConvertType(Request.QueryString["PageNo"] ?? "1", 1);
            string Cat = Request.QueryString["Cat"] ?? "";
            string query = Request.QueryString["query"] ?? "";
            return new TbkDgItemCouponGetRequest
            {
                Platform = Platform,
                PageSize = PageSize,
                PageNo = PageNo,
                Cat = Cat,
                Q = query,
            };
        }
        private TbkItemGetRequest GetGoodsItem()
        {
            int Platform = Tools.ConvertType(Request.QueryString["Platform"] ?? "1", 1);
            int PageSize = Tools.ConvertType(Request.QueryString["PageSize"] ?? "10", 10);
            int PageNo = Tools.ConvertType(Request.QueryString["PageNo"] ?? "1", 1);
            string Cat = Request.QueryString["Cat"] ?? "";
            string query = Request.QueryString["query"] ?? "";

            bool IsTmall = Tools.ConvertType(Request.QueryString["IsTmall"] ?? "false", false);
            string Fields = Request.QueryString["Fields"] ?? "num_iid,title,pict_url,small_images,reserve_price,zk_final_price,user_type,provcity,item_url,seller_id,volume,nick";
            string Sort = Request.QueryString["Sort"] ?? "tk_rate_des";
            long? StartPrice = Tools.ConvertType<long?>(Request.QueryString["StartPrice"], null);
            long? EndPrice = Tools.ConvertType<long?>(Request.QueryString["EndPrice"], null);

            return new TbkItemGetRequest
            {
                Fields = Fields,
                Platform = Platform,
                PageSize = PageSize,
                PageNo = PageNo,
                IsTmall = IsTmall,
                Sort = Sort,
                Cat = Cat,
                StartPrice = StartPrice,
                EndPrice = EndPrice,
                Q = query
            };
        }


        private TbkJuTqgGetRequest GetJuTqg()
        {
            int PageSize = Tools.ConvertType(Request.QueryString["PageSize"] ?? "10", 10);
            int PageNo = Tools.ConvertType(Request.QueryString["PageNo"] ?? "1", 1);
            string StartTime = Request.QueryString["StartTime"] ?? DateTime.Now.ToString("yyyy-MM-dd 09:mm:ss");
            string EndTime = Request.QueryString["EndTime"] ?? DateTime.Now.ToString("yyyy-MM-dd 16:mm:ss");
            return new TbkJuTqgGetRequest
            {
                Fields = "click_url,pic_url,reserve_price,zk_final_price,total_amount,sold_num,title,category_name,start_time,end_time",
                PageSize = PageSize,
                PageNo = PageNo,
                StartTime = Tools.ConvertType<DateTime?>(StartTime,null),
                EndTime = Tools.ConvertType<DateTime?>(EndTime, null)
            };
        }

        private Dictionary<string, string> TableMapKey(DataTable dt)
        {
            Dictionary<string, string> mapkey = new Dictionary<string, string>();
            mapkey.Add("卖家id", "seller_id");
            mapkey.Add("店铺名称", "shop_title");
            mapkey.Add("平台类型", "user_type");
            mapkey.Add("商品价格(单位：元)", "zk_final_price");
            mapkey.Add("商品名称", "title");
            mapkey.Add("卖家旺旺", "nick");
            mapkey.Add("商品月销量", "volume");
            mapkey.Add("商品主图", "pict_url");
            mapkey.Add("商品id", "num_iid");
            mapkey.Add("商品详情页链接地址", "item_url");
            mapkey.Add("优惠券总量", "coupon_total_count");
            mapkey.Add("收入比率(%)", "commission_rate");
            mapkey.Add("优惠券面额", "coupon_info");
            mapkey.Add("优惠券剩余量", "coupon_remain_count");
            mapkey.Add("优惠券开始时间", "coupon_start_time");
            mapkey.Add("优惠券结束时间", "coupon_end_time");
            mapkey.Add("优惠券链接", "coupon_click_url");
            mapkey.Add("商品优惠券推广链接", "coupon_receive_url");
            mapkey.Add("商品一级类目", "category_name");
            mapkey.Add("淘宝客链接", "tbk_url");
            mapkey.Add("优惠券id", "coupon_id");


            //mapkey.Add("item_description", "item_description");
            //mapkey.Add("category", "category");
            foreach (var item in mapkey)
            {
                if (dt.Columns.Contains(item.Key))
                {
                    dt.Columns[item.Key].ColumnName = item.Value;
                }
            }
            return mapkey;
        }
        private Dictionary<string,dynamic> XlsToDataBase()
        {
            string connstr = ConfigurationManager.ConnectionStrings["GgcmsDB"].ConnectionString;
            string path = (Request.QueryString["path"] ?? "/tbk/data.csv").ToLower();
            string file = Context.Server.MapPath(path);
            Dictionary<string, dynamic> result = new Dictionary<string, dynamic>();
            if (File.Exists(file))
            {
                try
                {
                    DataHelper helper = new DataHelper(connstr);
                    DataTable table = ExcelHelper.InputFromCSV(file);
                    var keys= TableMapKey(table);
                    List<string> cols = new List<string>();
                    List<string> vals= new List<string>();
                    foreach (var kv in keys)
                    {
                        cols.Add("[" + kv.Value + "]");
                        vals.Add("@" + kv.Value + "");
                    }
                    string insSql= "INSERT INTO [tbk_coupon] ("+string.Join(",", cols)+") VALUES  (" + string.Join(",", vals) + ");";
                    int count = 0;
                    foreach (DataRow row in table.Rows)
                    {
                        if(string.IsNullOrWhiteSpace(row["num_iid"].ToString()))
                        {
                            break;
                        }
                        //string sql = "delete from tbk_coupon where num_iid = " + row["num_iid"].ToString();
                        //helper.ExecuteNonQuery(sql);
                        List<SqlParameter> sqlParams = new List<SqlParameter>();
                        foreach (var item in keys)
                        {
                            if (table.Columns.Contains(item.Value))
                            {
                                object value = row[item.Value];
                                if(item.Value== "user_type")
                                {
                                    value = value.ToString() == "天猫" ? 1 : 0;
                                }
                                sqlParams.Add(new SqlParameter(item.Value, value));
                            }
                        }
                        try
                        {
                            helper.ExecuteNonQuery(insSql, CommandType.Text, sqlParams);
                            count++;
                        }
                        catch(Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                    }
                    result.Add("count", count);
                    return result;
                }
                catch(Exception ex)
                {
                    result.Add("error", -2);
                    result.Add("message", ex.Message);
                    return result;
                }

            }
            else
            {
                result.Add("error", -1);
                result.Add("message", "file not found");
                return result;
            }
        }
        /// <summary>
        /// 删除过期数据
        /// </summary>
        /// <returns></returns>
        private Dictionary<string, dynamic> ClearDataBase()
        {
            Dictionary<string, dynamic> result = new Dictionary<string, dynamic>();
            string connstr = ConfigurationManager.ConnectionStrings["GgcmsDB"].ConnectionString;
            DataHelper helper = new DataHelper(connstr);
            string sql = "delete from tbk_coupon where coupon_end_time < GETDATE()";
            try
            {
                int num = helper.ExecuteNonQuery(sql);
                result.Add("code", 0);
                result.Add("result", num);
                return result;
            }
            catch(Exception ex)
            {
                result.Add("code", "1");
                result.Add("message", ex.Message);
                return result;
            }
        }
        private List<Dictionary<string, dynamic>> GetSuperCoupon()
        {
            int PageSize = Tools.ConvertType(Request.QueryString["PageSize"] ?? "10", 10);
            int PageNo = Tools.ConvertType(Request.QueryString["PageNo"] ?? "1", 1);
            string Cat = DataHelper.DecodeOutputString(Request.QueryString["Cat"] ?? "");
            string query = DataHelper.DecodeOutputString(Request.QueryString["query"] ?? "");
            if (string.IsNullOrWhiteSpace(query)) {
                query = "coupon_end_time < GETDATE()";
            }
            else
            {
                query = "coupon_end_time < GETDATE() and [title] like '%" + query + "%' ";
            }
            bool IsTmall = Tools.ConvertType(Request.QueryString["IsTmall"] ?? "false", false);
            string Fields = DataHelper.DecodeOutputString(Request.QueryString["Fields"] ?? "");
            string Sort = DataHelper.DecodeOutputString(Request.QueryString["Sort"] ?? "id");
            string Order = DataHelper.DecodeOutputString(Request.QueryString["Order"] ?? "desc");

            List<Dictionary<string, dynamic>> result = new List<Dictionary<string, dynamic>>();
            string connstr = ConfigurationManager.ConnectionStrings["GgcmsDB"].ConnectionString;
            DataHelper helper = new DataHelper(connstr);
            DataTable db = helper.GetPageTable("tbk_coupon", Fields, query, Order, Sort, PageSize, PageNo);
            foreach(DataRow row in db.Rows)
            {
                Dictionary<string, dynamic> r = new Dictionary<string, dynamic>();
                foreach (DataColumn col in db.Columns)
                {
                    r.Add(col.ColumnName, row[col.ColumnName]);
                }
                result.Add(r);
            }
            return result;
        }
    }
}